package org
import org.apache.spark.sql.SparkSession
object sparkYun2_SQL {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .master("local[*]")
      .appName("spark")
      .getOrCreate()
    val sc = spark.sparkContext
    //RDD读取
    val scoreRDD = sc.textFile("src/main/resources/score.txt")
    scoreRDD.foreach(println)
    //sql读取 textFile只能读取为一个字段
    val scoreSQL =spark.read.textFile("src/main/resources/score.txt")
    val scoreFrame=spark.read.csv("src/main/resources/score.txt")
    //输出数据模式（列名和类型
    scoreFrame.printSchema()
    //scoreSQL.printSchema()
    //查看数据
    scoreFrame.show()
    //scoreSQL.show()
    //创建临时表
    scoreFrame.createTempView("score")
    //SQL语句 求最高分，总分和平均分
    val res1 = spark.sql(
      """
        |select
        | _c2 as course,
        | max(_c3) as max_score,
        | sum(_c3) as sum_score,
        | avg(_c3) as avg_score
        | from score
        | group by _c2
        |""".stripMargin)
    res1.show
    val res2 = spark.sql(
      """
        |select
        | _c1 as name
        | from score
        | where _c3 > 90
        |""".stripMargin)
    res2.show()
    val scoreYun2 = spark.read.option("encoding","GBK").option("header","true").csv("src/main/resources/23yun2.csv")
    scoreYun2.show()


    sc.stop()
  }

}
